Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# TRIGGER command

The TRIGGER command associates a previously-created program to an object and identifies the object event that automatically executes the program; or disassociates a trigger program from the object.

In order to assign a trigger program to an object, the object must be the one most recently defined or considered during the current session. When it is not, you must first use a CONSIDER statement to make it the current definition.

See also: "Trigger Programs".

Syntax

TRIGGER {event-name [program-name] }... | {DELETE event-name}... | DELETE ALL

where event-name is one of the following:

  MAINTAIN
  DELETE
  PROPERTY
  ASSIGN
  BEFORE_UPDATE
  AFTER_UPDATE

You can use the same keyword many times in a single TRIGGER statement; however, in this case, Oracle OLAP ignores all but the last occurrence of the keyword. See "Multiple Occurrences of the Same Keyword", for details.

Arguments

MAINTAIN

  Specifies that the trigger for the program is a Maintain event. A Maintain event is the execution of the MAINTAIN statement. As outlined in Table 24-2, "Subevents for the MAINTAIN Event", the Maintain event has several subevents that correspond to the major keywords of the MAINTAIN command. Exactly when a program triggered by a Maintain event is executed is dependent on the Maintain subevent that triggered the program and the object type for which the Maintain event is defined:.

  - Programs triggered by Maintain Add and Maintain Merge events on dimensions and composites are executed after the entire MAINTAIN statement executes.

  - Programs triggered by Maintain Add and Maintain Merge events on dimension surrogates are executed multiple times—once after each value is added or merged.

  - Programs triggered by other Maintain subevents are executed before the MAINTAIN statement is executed.

DELETE

  Specifies that the trigger for the program is a Delete event. A Delete event is a DELETE statement for the object. Oracle OLAP executes the specified program immediately before a DELETE statement deletes the object.

PROPERTY

  Specifies that Oracle OLAP executes the specified program in response to a Property event. A Property event is the execution of a PROPERTY statement to create, modify, or delete an object property. A program that is triggered by a Property event is executed before the statement that triggered it.

ASSIGN

  Specifies that Oracle OLAP executes the specified program in response to a Assign event. An Assign event is executed when SET assigns values to variable, relation, worksheet object, or a formula. A program that is triggered by SET is executed each time Oracle OLAP assigns a value to the object for which the event was defined. Thus, a program triggered by an Assign event is often executed over and over again as the assignment statements loops through a object assigning values.

UPDATE

  When the object has been acquired using ACQUIRE in an analytic workspace that is attached in multiwriter mode, specifies that Oracle OLAP executes the specified program immediately after the object is updated.

  Tip: To specify processing when the entire analytic workspace is updated, create a TRIGGER_AFTER_UPDATE or TRIGGER_BEFORE_UPDATE program.

program-name

  The name of the trigger program. When omitted for an event, the event does not trigger an action.

DELETE event-name

  Deletes the triggers for the specified object events. Oracle OLAP disassociates the trigger program from the specified object event.

DELETE ALL

  Deletes all of the triggers for the specified object. Oracle OLAP disassociates the trigger program from all events for object.

Notes

Multiple Occurrences of the Same Keyword

You can use all of the keywords in a single TRIGGER statement. However, if you use the same keyword twice in a TRIGGER statement, then Oracle OLAP recognized the last occurrence of the keyword; other occurrences are ignored.

For example, assume that you code the following TRIGGER statement.

    TRIGGER PROPERTY progname1 PROPERTY progname2 PROPERTY progname3

When executing this TRIGGER statement, Oracle OLAP executes `progname3` immediately before a property of the object is created, modified, or deleted; Oracle OLAP does not execute `progname1` or `progname2`.

Examples

Example 24-4 Creating Triggers

Assume that your analytic workspace contains a `TEXT` dimension named `city` and that you want to create programs that will automatically execute when a MAINTAIN statement executes against `city` or when a property is created or deleted for city. To create these triggers, you issue the following statements.

    "Define the trigger programs
    DEFINE trigger_maintain_move_city PROGRAM BOOLEAN
    DEFINE trigger_property_city PROGRAM BOOLEAN
    "Associate the trigger programs to events for the city dimension
    CONSIDER city
    TRIGGER PROPERTY trigger_property_city
    TRIGGER MAINTAIN trigger_maintain_move_city

Example 24-5 Describing Triggers

Assume that you have created the triggers for `city` as described in Example 24-4, "Creating Triggers" . Later you want to see the description of the triggers, to do so you cannot merely issue a DESCRIBE statement for your analytic workspace. Instead, you must issue a FULLDSC statement.

    DEFINE CITY DIMENSION TEXT
    TRIGGER MAINTAIN TRIGGER_MAINTAIN_MOVE_CITY -
            PROPERTY TRIGGER_PROPERTY_CITY

    DEFINE TRIGGER_MAINTAIN_MOVE_CITY PROGRAM BOOLEAN

    DEFINE TRIGGER_PROPERTY_CITY PROGRAM BOOLEAN

Example 24-6 Deleting Triggers

Assume that you have created the triggers described in Example 24-4, "Creating Triggers" . Now you want to delete the MAINTAIN trigger for `city`. To delete this trigger you issue the following statements.

    CONSIDER city
    TRIGGER DELETE MAINTAIN

When you issue a FULLDSC statement, you confirm that the MAINTAIN trigger for `city` has been deleted although the `trigger_maintain_move_city` program remains.

    DEFINE CITY DIMENSION TEXT
    TRIGGER PROPERTY TRIGGER_PROPERTY_CITY

    DEFINE TRIGGER_MAINTAIN_MOVE_CITY PROGRAM BOOLEAN

    DEFINE TRIGGER_PROPERTY_CITY PROGRAM BOOLEAN

To actually delete the `trigger_maintain_move_city` program you need to issue the following statement.

    DELETE TRIGGER_MAINTAIN_MOVE_CITY

Example 24-7 A MAINTAIN Trigger Program

Assume that you have a dimension with the following definition in your analytic workspace.

    DEFINE CITY DIMENSION TEXT

To create a Maintain trigger for city, you take the following steps:

1. Define the trigger program as a user-defined function. It can have any name that you want. The following statement defines a program named `trigger_maintain_city`.

       DEFINE trigger_maintain_city PROGRAM BOOLEAN

2. Specify the content of the program.

       PROGRAM
       SHOW JOINCHARS ('calltype = ' CALLTYPE)
       SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
       SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
       RETURN TRUE
       END

3. Issue a TRIGGER statement to associate the trigger program with the `city` dimension as a program to be executed when a Maintain event occurs. Remember to use a CONSIDER statement to make the definition for `city` the current definition.

       CONSIDER city
       TRIGGER MAINTAIN TRIGGER_MAINTAIN_CITY

When you issue a FULLDSC statement to see a full description of your analytic workspace, you can see the definition of `city` (including its Maintain trigger) and the `trigger_maintain_city` program.

    DEFINE CITY DIMENSION TEXT
    TRIGGER MAINTAIN TRIGGER_MAINTAIN_CITY

    DEFINE TRIGGER_MAINTAIN_CITY PROGRAM BOOLEAN
    PROGRAM
    SHOW JOINCHARS ('calltype = ' CALLTYPE)
    SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
    SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
    RETURN TRUE
    END

As illustrated in the following statements and output, when you issue MAINTAIN statements for city, the trigger_maintain_city program executes.

    MAINTAIN city ADD 'Boston' 'Houston' 'Dallas'

    calltype = TRIGGER
    triggering event = MAINTAIN
    triggering subevent = ADD

    REPORT city

    CITY
    --------------
    Boston
    Houston
    Dallas

    MAINTAIN city MOVE 'Dallas' to 2

    calltype = TRIGGER
    triggering event = MAINTAIN
    triggering subevent = MOVE

    REPORT city

    CITY
    --------------
    Boston
    Dallas
    Houston

Example 24-8 An ASSIGN Trigger on a Variable

Assume that your analytic workspace contains objects with the following definitions.

    DEFINE geog DIMENSION TEXT
    DEFINE sales VARIABLE DECIMAL <geog>
    DEFINE percent_sales VARIABLE INTEGER <geog>

The `sales` variable contains the following values.

    GEOG             SALES
    -------------- ----------
    North America        0.59
    Europe               9.35
    Asia                   NA

The `percent_sales` variable is empty.

Assume that you want specialized processing of values when you assign values to `percent_sales`. To handle this processing automatically, you can create a Assign trigger program for `percent_sales` by taking the following steps:

1. Create a trigger program that will execute each time you assign values to `percent_sales`.

       DEFINE TRIGGER_EQ PROGRAM BOOLEAN
       PROGRAM
       ARGUMENT datavalue WORKSHEET
       show 'description of triggering object = '
       FULLDESCRIBE &TRIGGER(NAME)
       SHOW JOINCHARS ('calltype = ' CALLTYPE)
       SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
       SHOW JOINCHARS ('triggering subevent = ' TRIGGER(SUBEVENT))
       SHOW JOINCHARS ('value being assigned = ' datavalue)
       SHOW '  '
       END

2. Add an assign trigger to `percent_sales` using a TRIGGER statement. Remember to first issue a CONSIDER statement to make the definition for the percent_sales variable the current definition.

       CONSIDER percent_sales
       TRIGGER ASSIGN TRIGGER_EQ

3. Assign values to `percent_sales`.

       percent_sales = (sales/TOTAL(sales))*100

   Assigning values to `percent_sales` triggers the execution of the `trigger_eq` program and produces the following output lines.

       description of triggering object =
       DEFINE PERCENT_SALES VARIABLE INTEGER <GEOG>
       TRIGGER ASSIGN TRIGGER_EQ
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value being assigned = 6

       description of triggering object =
       DEFINE PERCENT_SALES VARIABLE INTEGER <GEOG>
       TRIGGER ASSIGN TRIGGER_EQ
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value being assigned = 94

       description of triggering object =
       DEFINE PERCENT_SALES VARIABLE INTEGER <GEOG>
       TRIGGER ASSIGN TRIGGER_EQ
       calltype = TRIGGER
       triggering event = ASSIGN
       triggering subevent =
       value being assigned =

   Note: From the output you can see that Oracle OLAP called the `trigger_eq` program three times—each time it assigned a value to `percent_sales`.

4. When you issue REPORT commands for `sales` and `percent_sales` you can see the result of the calculations. The percent_sales variable contains values that are the percent of sales for each continent.

       GEOG                  SALES
       -------------- --------------------
       North America                  0.59
       Europe                         9.35
       Asia                             NA

       GEOG              PERCENT_SALES
       -------------- --------------------
       North America                     6
       Europe                           94
       Asia                             NA

Example 24-9 Setting Values in an ASSIGN Trigger Program

Assume that you have the following objects in your analytic workspace.

    DEFINE GEOGRAPHY DIMENSION TEXT WIDTH 12
    LD Geography Dimension Values

    DEFINE PRODUCT DIMENSION TEXT WIDTH 12
    LD Product Dimension Values

    DEFINE TIME DIMENSION TEXT WIDTH 12
    LD Time Dimension Values

    DEFINE CHANNEL DIMENSION TEXT WIDTH 12
    LD Channel Dimension Values

    DEFINE F.MARGIN FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME>
    LD Margin
    EQ f.sales-f.costs

    DEFINE F.COSTS VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME>
    LD Costs

    DEFINE F.SALES VARIABLE SHORT <GEOGRAPHY PRODUCT CHANNEL TIME>
    LD Sales

Note that `f.costs`, `f.sales`, and `f.margin` all have the same dimensions.

Now you add an Assign trigger to `f.margin` that will execute a program named `t.margin`. The definition of `f.margin` is modified to the following definition.

    DEFINE F.MARGIN FORMULA DECIMAL <CHANNEL GEOGRAPHY PRODUCT TIME>
    LD Margin
    TRIGGER ASSIGN T.MARGIN
    EQ f.sales-f.costs

Now you actually write the t.margin program. When an expression is assigned to the `f.margin` formula, the program uses this value to compute new values for `f.costs` and `f.sales`.

    DEFINE T.MARGIN PROGRAM
    PROGRAM
    ARG newVal DECIMAL       " The value passed to the program by the Assign trigger
    VARIABLE t.valDiff DECIMAL     " Difference between newVal and old value
    VARIABLE t.costInc DECIMAL     " Amount the difference makes to costs
    "show the value of newVal
    SHOW 'newVal = ' NONL
    SHOW newVal
    " Compute the difference between the current value and the new one
    t.valDiff = newVal - f.margin
    " Now increase costs proportional to their existing amounts
    t.costInc = (newVal - f.margin) * (f.costs/f.sales)
    " Adjust the values of sales and costs to get the new value
    SET1 f.costs = f.costs + t.costInc

    SET1 f.sales =  f.sales + t.valDiff + t.costInc

    SHOW geography NONL
    SHOW ' ' NONL
    SHOW product NONL
    SHOW ' ' NONL
    SHOW channel NONL
    SHOW ' ' NONL
    SHOW time NONL
    SHOW ' f.costs = 'NONL
    SHOW f.costs NONL
    SHOW ' f.sales = 'NONL
    SHOW f.sales
    END

Now assume that you issue the following LIMIT statements to identify a subset of data and issue a REPORT statement to report on the values of f.margin.

    LIMIT t0.hierdim TO 'STANDARD'
    LIMIT time TO t0.levelrel EQ 'L2'
    LIMIT geography TO FIRST 1
    LIMIT channel TO FIRST 1
    LIMIT product TO FIRST 5
    REPORT DOWN time ACROSS product: f.margin

    GEOGRAPHY: WORLD
    CHANNEL: TOTALCHANNEL
                   -----------------------F.MARGIN-----------------------
                   -----------------------PRODUCT------------------------
    TIME           TOTALPROD   AUDIODIV  PORTAUDIO    PORTCD     PORTST
    -------------- ---------- ---------- ---------- ---------- ----------
    Q1.96          54,713,974 29,603,546  5,379,661  2,480,914  1,615,708
    Q2.96          63,919,784 34,594,087  6,331,848  2,869,265  1,931,785
    Q3.96          58,303,490 31,543,152  5,792,725  2,616,515  1,795,701
    Q4.96          71,197,892 38,383,878  7,059,581  3,163,804  2,232,880
    Q1.97          55,489,723 29,989,262  5,368,237  2,491,475  1,607,344
    Q2.97          41,687,908 22,532,979  4,070,725  1,855,992  1,245,161

Now you issue the following assignment statement that increase the value of `f.margin` by 10% and report it

    f.margin = f.margin * 1.1

The execution of this assignment statement triggers the execution of the Assign trigger program named `t.margin`. The output of that program follows.

    newVal = 60,185,371.40
    WORLD TOTALPROD TOTALCHANNEL Q1.96 f.costs = 1,298,474.00 f.sales = 61,483,840.00
    newVal = 32,563,900.67
    WORLD AUDIODIV TOTALCHANNEL Q1.96 f.costs = 664,226.90 f.sales = 33,228,130.00
    newVal = 5,917,626.67
    WORLD PORTAUDIO TOTALCHANNEL Q1.96 f.costs = 97,976.04 f.sales = 6,015,603.00
    newVal = 2,729,005.43
    WORLD PORTCD TOTALCHANNEL Q1.96 f.costs = 34,301.53 f.sales = 2,763,307.00
    newVal = 1,777,278.95
    WORLD PORTST TOTALCHANNEL Q1.96 f.costs = 25,160.72 f.sales = 1,802,440.00
    newVal = 70,311,762.13
    WORLD TOTALPROD TOTALCHANNEL Q2.96 f.costs = 1,504,051.00 f.sales = 71,815,820.00
    newVal = 38,053,495.70
    WORLD AUDIODIV TOTALCHANNEL Q2.96 f.costs = 768,788.10 f.sales = 38,822,280.00
    newVal = 6,965,032.86
    WORLD PORTAUDIO TOTALCHANNEL Q2.96 f.costs = 114,558.20 f.sales = 7,079,591.00
    newVal = 3,156,191.20
    WORLD PORTCD TOTALCHANNEL Q2.96 f.costs = 39,256.88 f.sales = 3,195,448.00
    newVal = 2,124,963.02
    WORLD PORTST TOTALCHANNEL Q2.96 f.costs = 29,780.54 f.sales = 2,154,744.00
    newVal = 64,133,838.86
    WORLD TOTALPROD TOTALCHANNEL Q3.96 f.costs = 1,350,733.00 f.sales = 65,484,570.00
    newVal = 34,697,467.06
    WORLD AUDIODIV TOTALCHANNEL Q3.96 f.costs = 691,887.10 f.sales = 35,389,360.00
    newVal = 6,371,997.63
    WORLD PORTAUDIO TOTALCHANNEL Q3.96 f.costs = 103,203.70 f.sales = 6,475,202.00
    newVal = 2,878,166.40
    WORLD PORTCD TOTALCHANNEL Q3.96 f.costs = 35,358.18 f.sales = 2,913,525.00
    newVal = 1,975,270.68
    WORLD PORTST TOTALCHANNEL Q3.96 f.costs = 27,339.77 f.sales = 2,002,611.00
    newVal = 78,317,681.06
    WORLD TOTALPROD TOTALCHANNEL Q4.96 f.costs = 1,618,915.00 f.sales = 79,936,590.00
    newVal = 42,222,265.94
    WORLD AUDIODIV TOTALCHANNEL Q4.96 f.costs = 826,923.40 f.sales = 43,049,190.00
    newVal = 7,765,539.34
    WORLD PORTAUDIO TOTALCHANNEL Q4.96 f.costs = 123,269.50 f.sales = 7,888,809.00
    newVal = 3,480,184.35
    WORLD PORTCD TOTALCHANNEL Q4.96 f.costs = 41,998.90 f.sales = 3,522,183.00
    newVal = 2,456,168.00
    WORLD PORTST TOTALCHANNEL Q4.96 f.costs = 33,357.19 f.sales = 2,489,525.00
    newVal = 61,038,695.03
    WORLD TOTALPROD TOTALCHANNEL Q1.97 f.costs = 1,423,963.00 f.sales = 62,462,660.00
    newVal = 32,988,187.65
    WORLD AUDIODIV TOTALCHANNEL Q1.97 f.costs = 679,477.80 f.sales = 33,667,660.00
    newVal = 5,905,060.56
    WORLD PORTAUDIO TOTALCHANNEL Q1.97 f.costs = 158,854.40 f.sales = 6,063,915.00
    newVal = 2,740,622.56
    WORLD PORTCD TOTALCHANNEL Q1.97 f.costs = 53,144.41 f.sales = 2,793,767.00
    newVal = 1,768,078.14
    WORLD PORTST TOTALCHANNEL Q1.97 f.costs = 40,784.62 f.sales = 1,808,863.00
    newVal = 45,856,698.46
    WORLD TOTALPROD TOTALCHANNEL Q2.97 f.costs = 1,070,465.00 f.sales = 46,927,160.00
    newVal = 24,786,276.35
    WORLD AUDIODIV TOTALCHANNEL Q2.97 f.costs = 512,435.60 f.sales = 25,298,710.00
    newVal = 4,477,797.64
    WORLD PORTAUDIO TOTALCHANNEL Q2.97 f.costs = 118,791.70 f.sales = 4,596,590.00
    newVal = 2,041,591.56
    WORLD PORTCD TOTALCHANNEL Q2.97 f.costs = 39,287.77 f.sales = 2,080,879.00
    newVal = 1,369,677.57
    WORLD PORTST TOTALCHANNEL Q2.97 f.costs = 30,038.08 f.sales = 1,399,716.00

Example 24-10 An ASSIGN Trigger on a Formula

The way Oracle OLAP handles assigning values to a formula varies depending on whether or not the formula has an Assign trigger as part of its definition.

Assume your analytic workspace contains objects with the following definitions and values.

    DEFINE geog.d DIMENSION TEXT
    DEFINE time.d DIMENSION TEXT
    DEFINE sales VARIABLE DECIMAL <time.d geog.d>
    DEFINE f_modified_sales FORMULA DECIMAL <time.d geog.d>
      EQ sales+20

A report of `sales`, shows the base values.

    -------------------SALES-------------------
                   ------------------TIME.D-------------------
    GEOG.D          Jan2004    Feb2004    Mar2004      2004
    -------------- ---------- ---------- ---------- ----------
    Boston               4.00       4.66       5.91       NA
    Medford              4.37       5.80       4.45       NA
    San Diego            4.97       5.95       4.75       NA
    Sunnydale            5.85       5.26       4.08       NA

A report of `f_modified_sales` formula displays the following report that contains the values computed by the formula.

    -------------F_MODIFIED_SALES--------------
                   ------------------TIME.D-------------------
    GEOG.D          Jan2004    Feb2004    Mar2004      2004
    -------------- ---------- ---------- ---------- ----------
    Boston              24.00      24.66      25.91         NA
    Medford             24.37      25.80      24.45         NA
    San Diego           24.97      25.95      24.75         NA
    Sunnydale           25.85      25.26      24.08         NA

The `f_modified_sales` formula does not presently have an Assign trigger on it. Consequently, as illustrated in the following code, any attempt to assign values to `f_modified_sales` results in an error.

    f_modified_sales = 3
    ORA-34142: You cannot assign values to a FORMULA.

To create an Assign trigger on f_modified_sales take the following steps:

1. Define the trigger program

       DEFINE TRIGGER_ASSIGN_MODIFIED_SALES PROGRAM
       PROGRAM
       ARGUMENT datavalue NUMBER
       SHOW 'description of triggering object = '
       DESCRIBE &TRIGGER(NAME)
       SHOW JOINCHARS ('calltype = ' CALLTYPE)
       SHOW JOINCHARS ('triggering event = ' TRIGGER(EVENT))
       SHOW JOINCHARS ('value being assigned = ' datavalue)
       SHOW '  '
       END

2. Add the Assign trigger to the definition of the formula using the following statements.

       CONSIDER f_modified_sales
       TRIGGER ASSIGN trigger_assign_modified_sales

   Issuing a `FULLDSC f_modified_sales` statement displays the new complete definition for `f_modified_sales`.

       DEFINE F_MODIFIED_SALES FORMULA DECIMAL <TIME.D GEOG.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       EQ sales+20

3. Now when you issue the following statement to assign a value to `f_modified_sales`, an error does not occur. Instead, the `trigger_assign_modified_sales` trigger program executes 16 times, once for each dimension value of `sales`.

       f_modified_sales = 3

       description of triggering object =
       DEFINE F_MODIFIED_SALES FORMULA DECIMAL <TIME.D GEOG.D>
       TRIGGER ASSIGN TRIGGER_ASSIGN_MODIFIED_SALES
       EQ sales+20
       calltype = TRIGGER
       triggering event = ASSIGN
       value being assigned = 3.00
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...
       description of triggering object =
       ...

4. However, as issuing a REPORT statement for `f_modified_sales` illustrates, the values calculated by a simple execution of the formula have not changed.

       report f_modified_sales

                      -------------F_MODIFIED_SALES--------------
                      ------------------TIME.D-------------------
       GEOG.D          Jan2004    Feb2004    Mar2004      2004
       -------------- ---------- ---------- ---------- ----------
       Boston              24.00      24.66      25.91         NA
       Medford             24.37      25.80      24.45         NA
       San Diego           24.97      25.95      24.75         NA
       Sunnydale           25.85      25.26      24.08         NA